/*Base de datos: AI2011G09
Script para generar la base de datos en Sql Server
Login: AI2011G09
Pass: AI2011G09

*/

USE [master]
GO

--Creo la base
CREATE DATABASE AI2011G09
GO

--Un login
CREATE LOGIN AI2011G09
	WITH PASSWORD = 'AI2011G09',
	DEFAULT_DATABASE = AI2011G09,
	CHECK_EXPIRATION = OFF,
	CHECK_POLICY = OFF
GO

use AI2011G09
GO

exec SP_CHANGEDBOWNER 'AI2011G09'
GO

--Un usuario
CREATE USER AdministradorCosechaUsr
	FOR LOGIN AI2011G09
GO

--Y las tablas
CREATE TABLE mineral
(
	codigo          varchar(50) PRIMARY KEY NOT NULL,	
	nombre 	        varchar(50) NOT NULL,
	activo	        char(1) NOT NULL -- 'T' o 'F'
);

CREATE TABLE propietario
(
	cuit		varchar(50) PRIMARY KEY NOT NULL,
	razonSocial 	varchar(50) NOT NULL,
	contacto	varchar(50)
);

CREATE TABLE cereal
(
	nombre		varchar(50) PRIMARY KEY NOT NULL,
	periodoSiemb    integer NOT NULL,
	rindeTn		float NOT NULL,
	codMineralPrinc	varchar(50) NOT NULL,
	activo		char(1) NOT NULL,
	porcDesperdicio	float NOT NULL,
	esFino		char(1) NOT NULL -- 'T' o 'F'
);

CREATE TABLE lote
(
	nro			integer PRIMARY KEY NOT NULL,
	nroLotePadre		integer,
	areaSiembra	 	float NOT NULL,
	cuitProp		varchar(50) NOT NULL,
	sembrado		char(1), -- 'T' o 'F'
	habilitado		char(1), -- 'T' o 'F'
	anioUltSiembra		integer,
	periodoUltSiembra	integer,
	nombreUltCereal		varchar(50),
	activo			char(1) NOT NULL, -- 'T' o 'F'
	esSimple		char(1) NOT NULL -- 'T' o 'F'
);


CREATE TABLE itemMineralLote
(
	codMineral		varchar(50) NOT NULL,	
	nroLote			integer NOT NULL,
	porcentaje	 	float NOT NULL,
        PRIMARY KEY (codMineral, nroLote)
);

CREATE TABLE siembra
(
	anioSiembra              integer NOT NULL,
	periodoSiembra          integer NOT NULL,
	nombreCereal               varchar(50) NOT NULL,
        anioCosecha              integer,
        periodoCosecha          integer,
        PRIMARY KEY (anioSiembra, periodoSiembra, nombreCereal)
);


CREATE TABLE itemSiembra
(
	anio			integer NOT NULL,
	periodo		 	integer NOT NULL,
	nombreCereal    varchar(50) NOT NULL,
	nroLote			integer NOT NULL,
	cantSembrada	        float   NOT NULL,
        PRIMARY KEY (anio, periodo, nombreCereal, cantSembrada)
);


CREATE TABLE cosecha
(
	anio			integer NOT NULL,
	periodo		 	integer NOT NULL,
	nombreCereal    varchar(50) NOT NULL,
        PRIMARY KEY (anio, periodo, nombreCereal)
);


CREATE TABLE itemCosecha
(
	anio			integer NOT NULL,
	periodo		 	integer NOT NULL,
	nombreCereal    varchar(50) NOT NULL,
	nroLote			integer NOT NULL,
	cantCosechada	        float   NOT NULL,
        PRIMARY KEY (anio, periodo, nombreCereal, cantCosechada)
);


ALTER TABLE cereal ADD CONSTRAINT fk_cereal_mineral
	FOREIGN KEY(codMineralPrinc) REFERENCES mineral(codigo);


ALTER TABLE lote ADD CONSTRAINT fk_lote_lotePadre
	FOREIGN KEY(nroLotePadre) REFERENCES lote(nro);
ALTER TABLE lote ADD CONSTRAINT fk_lote_propietario
	FOREIGN KEY(cuitProp) REFERENCES propietario(cuit);
ALTER TABLE lote ADD CONSTRAINT fk_lote_siembra
	FOREIGN KEY(anioUltSiembra, periodoUltSiembra, nombreUltCereal) REFERENCES siembra(anioSiembra, periodoSiembra, nombreCereal);


ALTER TABLE itemMineralLote ADD CONSTRAINT fk_itemMineralLote_mineral 
	FOREIGN KEY(codMineral) REFERENCES mineral(codigo);
ALTER TABLE itemMineralLote ADD CONSTRAINT fk_itemMineralLote_lote 
	FOREIGN KEY(nroLote) REFERENCES lote(nro);


ALTER TABLE siembra ADD CONSTRAINT fk_siembra_cereal
	FOREIGN KEY(nombreCereal) REFERENCES cereal(nombre);
ALTER TABLE siembra ADD CONSTRAINT fk_siembra_cosecha
	FOREIGN KEY(anioCosecha, periodoCosecha, nombreCereal) REFERENCES cosecha(anio, periodo, nombreCereal);


ALTER TABLE itemSiembra ADD CONSTRAINT fk_itemSiembra_siembra
	FOREIGN KEY(anio, periodo, nombreCereal) REFERENCES siembra(anioSiembra, periodoSiembra, nombreCereal);
ALTER TABLE itemSiembra ADD CONSTRAINT fk_itemSiembra_lote
	FOREIGN KEY(nroLote) REFERENCES lote(nro);


ALTER TABLE itemCosecha ADD CONSTRAINT fk_itemCosecha_cosecha
	FOREIGN KEY(anio, periodo, nombreCereal) REFERENCES cosecha(anio, periodo, nombreCereal);
ALTER TABLE itemCosecha ADD CONSTRAINT fk_itemCosecha_lote
	FOREIGN KEY(nroLote) REFERENCES lote(nro);
	
---------
--Insert de registros de prueba

insert into mineral values('Fe', 'Hierro', 'T')
insert into mineral values('Zn','Zinc', 'T')
insert into mineral values('Cu','Cobre', 'T')

insert into propietario values ('20-125094-0', 'Leonardo Gonzalez', '125094')
insert into propietario values ('20-120676-0', 'Pablo Di Paoli', '120676')
insert into propietario values ('30-126345-0', 'Mariela Rotger', '126345')
insert into propietario values ('20-135724-0', 'Andres Orellano', '135724')
insert into propietario values ('30-135907-0', 'Laura Rico', '135907')
insert into propietario values ('20-1015009-0', 'Pablo Arrieta', '1015009')

insert into cereal values ('Maiz', 0, 15, 'Fe', 'T', 0, 'T')
insert into cereal values ('Soja', 1, 20, 'Cu', 'T', 8.5, 'F')

insert into lote values (1, null, 200.5, '20-125094-0','F', 'T', 0, 0, null, 'T', 'T');
insert into itemMineralLote values ('Fe', 1, 50.0)
insert into itemMineralLote values ('Cu', 1, 10.0)

insert into lote values (2, null, 300, '20-120676-0','F', 'T', 0, 0, null, 'T', 'T');
insert into itemMineralLote values ('Fe', 2, 30.0)
insert into itemMineralLote values ('Cu', 2, 30.0)

insert into lote values (3, null, 200.5, '20-125094-0','F', 'T', 0, 0, null, 'T', 'T');
insert into itemMineralLote values ('Fe', 3, 50.0)
insert into itemMineralLote values ('Cu', 3, 40.0)

insert into lote values (4, null, 0, '20-125094-0','F', 'T', 0, 0, null, 'T', 'F');
update lote set nroLotePadre = 4, habilitado = 'F' where nro in (1,3)

